const CryptoJS = require('crypto-js')
const {QueryTypes, db, Op, } = require("../models/db");
const _ = require("lodash");
const xe = require("xe-utils");
const v4 =  require("uuid");
const fs = require("fs");
const xlsx = require("node-xlsx");
const qiniu = require("qiniu");
const cnchar = require("cnchar");
const fileSaveDir = 'D:/sanzhi/down/web' // 导出文件保存的目录
let downUrl = 'https://download.sanzhiyun.cn' // 下载地址
const dayjs = require('dayjs')
const weekday = require('dayjs/plugin/weekday')
require('dayjs/locale/zh-cn')
const serieal = require("serialize-javascript");
const Np = require('number-precision')
Np.enableBoundaryChecking()
dayjs.extend(weekday)
dayjs.locale('zh-cn')
let tool = {
    downUrl: downUrl,
    fileSaveDir: fileSaveDir,
    xe: xe,
    Np: Np,
    _: _,
    dayjs: dayjs,
    add: (...vars) => {
        let len = vars.length
        let sum = 0
        for (let i = 0; i < len; i++) {
            sum = xe.add(sum,vars[i])
        }
        return sum
    },
    minus: (...vars) => {
        let len = vars.length
        let sum = vars[0]
        for (let i = 1; i < len; i++) {
            sum = xe.subtract(sum,vars[i])
        }
        return sum
    },
    multiply: (...vars) => {
        let len = vars.length
        let sum = vars[0]
        for (let i = 1; i < len; i++) {
            sum = xe.multiply(sum,vars[i])
        }
        return sum
    },
    divide: (...vars) => {
        let len = vars.length
        let sum = vars[0]
        for (let i = 1; i < len; i++) {
            sum = xe.divide(sum,vars[i])
        }
        return sum
    },
    obj2str: (obj) => {
        return serieal(obj)
    },
    str2obj: (str) => {
        return new Function('return ' + str)()
    },
    leftShop: async (id, autoLeft = 0) => {
        let model = await db.models['contactBf'].findOne({
            where: {
                id: id
            }
        })
        if(!model) return {
            stat: false,
            data: '未找到到店记录'
        }
        let leftTime = tool.getTime()
        if(tool.dayjs(leftTime).isBefore(model.arrived)) {
            return {stat: false, data: '离店时间返回错误，请再试一次'}
        }
        let dur = tool.dayjs(leftTime).unix() - tool.dayjs(model.arrived).unix()
        model.leftTime = leftTime
        model.state = '已完成'
        model.duration = dur
        model.durationStr = tool.secondsToStr(dur)
        model.autoLeft = autoLeft
        await model.save()
        return {stat: true, data: model.leftTime}
    },
    login: async (username, userpwd, ctx) => {
        let allowErrCount = 6; // 允许错误次数
        let user = await db.models['admin'].findOne({
            where: {
                username: username
            }
        })
        if(!user) {
            user = await db.models['admin'].findOne({
                where: {
                    mobile: username
                }
            })
        }
        if (!user) return ctx.err('未找到该用户');
        // return ctx.err('err')
        let errCount = user.errCount; // 已存在的错误次数
        errCount = errCount ? errCount : 0;
        if (errCount > (allowErrCount - 1)) {
            return ctx.err('错误次数超过 ' + allowErrCount + ' 次,账户已被锁定');
        }
        if (userpwd.length > 20) {
            if (user.userpwd !== userpwd) {
                // 增加错误次数
                user.errCount = errCount + 1
                await user.save()
                return ctx.err('用户名或密码错误');
            }
        }
        else {
            if (user.userpwd !== tool.crypto.MD5(userpwd)) {
                // 增加错误次数
                user.errCount = errCount + 1
                await user.save()
                return ctx.err('用户名或密码错误');
            }
        }
        // 错误次数大于0时，当正确后清空错误次数
        if (errCount > 0) {
            user.errCount = 0
            await user.save()
        }
        if (user.disabled === 2) {
            return ctx.err('用户已被删除')
        }
        if (user.disabled === 1) {
            return ctx.err('账号已被禁用')
        }
        let tenantId = user.tenantId;
        let tenant = await db.models['tenant'].findOne({
            where: {id: tenantId}
        })
        let ob = await db.query(`select * from options where tenantId=${tenantId} and name='system' `, {type: QueryTypes.SELECT, plain: true})

        let ret = user.toJSON()
        ret.isCard = tenant.isCard
        ret.tenantName = tenant.name
        if(user.roleId) {
            let role = await db.models['role'].findOne({
                where: {id: user.roleId}
            })
            if(role) ret.roleName = role.name
        }
        if (ob) {
            ret.options = ob.value
        }
        return ctx.suc('登录成功', ret);
    },
    getDay: (day= '', diff = 0, addType = 'DAY', format = 'YYYY-MM-DD') => {
        return dayjs(day?day:dayjs()).add(diff, addType).format(format)
    },
    getTime: (day= '', diff = 0, addType = 'day', format = 'YYYY-MM-DD HH:mm:ss') => {
        return  dayjs(day ? day : dayjs()).add(diff, addType).format(format)
    },
    getDayStart: (current = '', type = 'beg', diff = 0, unit = 'MONTH') => {
        if (type === 'beg'){
            return   dayjs(current ? current : dayjs()).add(diff, unit).startOf(unit).format('YYYY-MM-DD')
        }
        if (type === 'end'){
            return   dayjs(current ? current : dayjs()).add(diff, unit).endOf(unit).format('YYYY-MM-DD')
        }
    },
    crypto: {
        //MD5加密
        MD5(data) {
            return CryptoJS.MD5(data).toString()
        },
    },
    // 秒转时间
    secondsToStr: (seconds) => {
        let dayS = 24 * 60 * 60
        let day = parseInt(seconds/dayS)
        let l = seconds%dayS
        let hour = parseInt(l/(60*60))
        let l1 = l%(60*60)
        let minute = parseInt(l1/60)
        let sec = l1%60
        let str = '' //`${day}天${hour}时${minute}分${sec}秒`
        if(day > 0) str += day+'天'
        if(hour > 0) str += hour+'时'
        if(minute > 0) str += minute+'分'
        if(sec > 0) str += sec+'秒'
        return str
    },
    // 7牛云文件下载地址
    getPicUrl: (fileName, waterText = null, zoom = false) => {
        let returnWidth = 1200
        let returnHeight = 1200
        let returnQuantity = 90
        let roomW = 200
        const deadline = dayjs().unix() + 600; // 过期时间  10分钟
        let privateBucketDomain = 'https://qiniu.sanzhiyun.cn'
        const accessKey = 'Qv_OXaTQUf1Gi2dvH1Qe77wtLOdMuNMSPCUXzK6j';
        const secretKey = 'pEVQBydHGAZFEmG98ai6UJLL_TsdP-4dd0CxSXNF';
        const mac = new qiniu.auth.digest.Mac(accessKey, secretKey);
        const config = new qiniu.conf.Config();
        const bucketManager = new qiniu.rs.BucketManager(mac, config);
        let zooms = `imageView2/2/w/${roomW}/h/${roomW}/q/${returnQuantity}`
        let zoomsDefault = `imageView2/2/w/${returnWidth}/h/${returnHeight}/q/${returnQuantity}`
        let obj = {
            url: '',
            urlZoom: ''
        }
        let or = `${fileName}`
        let zo = `${fileName}`
        if(waterText) {
            or = or + `?watermark/4/text/${qiniu.util.urlsafeBase64Encode(waterText)}/fontsize/800/fill/${qiniu.util.urlsafeBase64Encode('white')}/dissolve/50/rotate/-40/uw/300/uh/300/resize/1|${zoomsDefault}`
            zo = zo + `?watermark/4/text/${qiniu.util.urlsafeBase64Encode(waterText)}/fontsize/800/fill/${qiniu.util.urlsafeBase64Encode('white')}/dissolve/50/rotate/-40/uw/300/uh/300/resize/1`
        }
        if (zoom) {
            if(waterText) {
                zo = zo + `|${zooms}`
            }
            else {
                zo = zo + `?${zooms}`
            }
        } else {
            // 默认缩放大小
            if(waterText) {
                zo = zo + `|${zoomsDefault}`
            }
            else {
                zo = zo + `?${zoomsDefault}`
            }
        }
        obj.url = bucketManager.privateDownloadUrl(privateBucketDomain, or, deadline);
        obj.urlZoom = bucketManager.privateDownloadUrl(privateBucketDomain, zo, deadline);
        return obj
    },
    uuid: () => {
        return v4.v4();
    },
    py: (str) => {
        return cnchar.spell(str, 'first', 'low')
    },
    getDistance (lng1, lat1, lng2, lat2) {
        let radLat1 = lat1*Math.PI / 180.0;
        let radLat2 = lat2*Math.PI / 180.0;
        let a = radLat1 - radLat2;
        let b = lng1*Math.PI / 180.0 - lng2*Math.PI / 180.0;
        let s = 2 * Math.asin(Math.sqrt(Math.pow(Math.sin(a/2),2) +
            Math.cos(radLat1)*Math.cos(radLat2)*Math.pow(Math.sin(b/2),2)));
        s = s *6378.137 *  1000 ;// EARTH_RADIUS;
        return tool.xe.round(s, 4);
    },
    toArrayTree: (ret) => {
        ret.map(row => {
            row.pid = row.pid ? row.pid : null
        })
        return xe.toArrayTree(ret, { parentKey: "pid", strict: true });
    },
    // 权限SQL
    authSql: (ctx) => {
        if (ctx.user) {
            let allData = ctx.user.allData
            let selfData = ctx.user.selfData
            let roleName = ctx.user.roleName
            let uid = ctx.user.id
            let tid = ctx.user.tenantId
            if (allData) {
                return ''
            }
            else if (roleName === '管理员') {
                return ''
            }
            else if (selfData) {
                return ` select ${uid} uid `
            }
            else {
                let auth = `SELECT b.id uid from admin_data_dept a inner join admin b on a.tenantId=${tid} and a.deptId=b.deptId and selfId=${uid} and b.tenantId=${tid} 
                union all SELECT owerId uid from admin_data_ower where tenantId=${tid} and selfId=${uid} union all  select ${uid} uid `
                return auth
            }
        }
        else {
             throw new Error('权限认证失败')
        }
    },
    getMenu: async (ctx) =>{
        let menu = [];
        let user = ctx.user;
        let uid = user.id;
        if(user.username === 'admin') {
            menu = await db.models['menuV5'].findAll({
                order: ['sort']
            })
        } else {
            // let isMan = await db.query(`select * from users a inner join role b on a.roleid=b.id and a.id='${uid}' and b.name='管理员'`, {type: QueryTypes.SELECT, plain: true});
            // if(isMan) {
            //     menu = db.models['menuV5'].findAll({
            //         where: {
            //             isAdmin: 0
            //         },
            //         order: ['sort']
            //     })
            // } else {
            //     let menusModel = await db.query(`select b.menus from users a inner join role b on a.roleid=b.id and a.id='${uid}'`, {type: QueryTypes.SELECT, plain: true})
            //     let mid = []
            //     if (menusModel) {
            //         let ms =  menusModel.menus
            //         for (let ar in ms) {
            //             mid.push(ar)
            //         }
            //     }
            //     if(mid.length > 0) {
            //         menu = db.models['menuV5'].findAll({
            //             where: {
            //                 id: {
            //                     [Op.in]: mid
            //                 }
            //             }
            //         })
            //     }
            // }
        }
        return menu
    },
    // 取关联公司
    relCompany: async (tenantId) => {
        let list1 = []
        let list = await db.models['tenantCompany'].findAll({
            where: {
                tenantId: tenantId
            },
            attributes: ['companyId']
        })
        list.map(row => {
            list1.push(row.companyId)
        })
        return  list1;
    },
    // 待配送列表
    unDeliver: async (cargoId) => {
        let sqls = `SELECT c.tenantId, c.iid, b.cargoId,d.billNo,d.billDate,co.name buName,co.address,co.linkMobile,co.linkName,c.invName,c.spec,
       c.unit,c.calNum,
    c.qty,c.unit1,c.unit2,c.invId,d.transType,c.hjName  from cargo_invoice b
    INNER JOIN v_main_info c on b.iid=c.iid
    inner join invoice d on b.iid=d.id
    inner join contact as co on d.buId=co.id    
    where b.cargoId in(${cargoId}) and c.trans<>N'换入' `
    // where b.cargoId in(${cargoId}) and c.trans<>N'换入' order by c.hjName `
        return await db.query(sqls, {type: QueryTypes.SELECT})
    },
    autoLeft: async () => {
        try {
            let list = await db.query(`select * from contact_bf where state='未离店'`, {type: QueryTypes.SELECT})
            await db.transaction(async () => {
                for (let i = 0; i < list.length; i++) {
                    let row = list[i]
                    let leftTime = row.arrived.substring(0, 10) + ` ${tool.getTime().substring(11)}`
                    let dur = tool.dayjs(leftTime).unix() - tool.dayjs(row.arrived).unix()
                    let model = await db.models['contactBf'].findOne({
                        where: {
                            id: row.id
                        }
                    })
                    model.leftTime = leftTime
                    model.state = '已完成'
                    model.duration = dur
                    model.durationStr = tool.secondsToStr(dur)
                    model.autoLeft = 2
                    await model.save()
                }
            })
        } catch (e) {

        }
    },
    // 取子分类的IDs
    getChildIds: async (id, tbName) => {
        let sql = `
            with cte_child(id,name,pid,level) 
            as
            ( 
              --起始条件 
              select id,name,pid,0 as level
              from ${tbName} 
              where id = ${id}
              union all
              --递归条件 
              select a.id,a.name,a.pid,b.level+1 
              from ${tbName} a 
              inner join 
              cte_child b 
              on ( a.pid=b.id)  
            ) 
            select * from cte_child;
        `
        let list = await db.query(sql, {type: QueryTypes.SELECT})
        let arr = []
        list.map(row => {
            arr.push(row.id)
        })
        return arr
    },
    // 表模型设置默认值
    setDefault: (data, mod) => {
        let attrs = db.models[mod].getAttributes()
        for(let i in attrs) {
            let dataType = attrs[i].type.getDataTypeId()
            if(dataType === 'INTEGER' || dataType === 'TINYINT' || dataType === 'SMALLINT' || dataType === 'DECIMAL') {
                if(!data[i]) data[i] = 0
                if(data[i] == undefined) data[i] = 0
                if(data[i] == 'undefined') data[i] = 0
                if(data[i] == 'null') data[i] = 0
                if(data[i] == null) data[i] = 0
            }
            if(dataType === 'STRING' || dataType === 'TEXT') {
                if(!data[i]) data[i] = ''
                if(data[i] == undefined) data[i] = ''
                if(data[i] == 'undefined') data[i] = ''
                if(data[i] == 'null') data[i] = ''
                if(data[i] == null) data[i] = ''
            }
        }
    },
    currentZq: async (tenantId) => {
        let zq = await db.models['zq'].findOne({
            where: {
                tenantId
            },
            order: [['id','desc']]
        })
        if (!zq) throw Error('请先启用账套')
        return zq;
    },
    zqIsStart: async (tenantId) => {
        return  await db.models['zq'].findOne({
            where: {tenantId}
        })
    },
    calAssQty: (qty,spec,unit1,unit2) => {
        let isneg = false
        if(qty < 0) isneg = true
        qty = Math.abs(qty)
        if(!spec) throw Error('规格不能为 0 ')
        let mai = Math.floor(qty/spec) > 0 ? `${Math.floor(qty/spec)}${unit1}`:''
        let s = tool.xe.toInteger(qty%spec)
        let sub = qty%spec > 0 ? `${s}${unit2}` : ''
        return isneg ? ('-' + (mai+sub)) : (mai+sub)
    },
    getFields: (obj) => {
        let fields = [
            {type: 'seq', width: 50, fixed: 'left'},
        ]
        if(!obj.hideCheckbox) {
            fields.push({type: 'selection', width: 40, fixed: 'left',align: 'center'})
        }
        obj.fields.map(col => {
            if(col.visible) {
                if(col.filter) {
                    col.filterRender = {name: 'FExtend'}
                    col.filters = [{data: {vals: [], sVal: '', fMenu: '', f1Type:'', f1Val: '', fMode: 'and', f2Type: '', f2Val: ''}}]
                }
                col.headerAlign = 'center'
                fields.push(col)
            }
        })
        // 加行操作按钮
        if(obj.rowBtn) {
            fields.push(tool.str2obj(obj.rowBtn))
        }
        // 搜索组件规则
        let searchFields = []
        obj.fields.map(row => {
            if(row.condition && row.field !== 'billState') {
                let rule = {
                    field: row.field,
                    title: row.title,
                    props: {
                        clearable: true,
                        cat: row.cat,
                        width: 150,
                    }
                }
                if(row.component) {
                    rule.com = row.component
                    if(row.component === 'jauto' ||row.component === 'JAuto' || row.component === 'JSelect' ) {
                        rule.com = 'JAuto'
                        if (row.list) {
                            rule.props.list = tool.str2obj(row.list)
                        }
                        let idField = row.idField
                        searchFields.push({
                            hide: true,
                            field: idField
                        })

                        let objStr = `{change:(inject,val)=>{
                            setTimeout(()=>{inject.$f.setValue('${idField}', val ? val.id : null);},10)
                        } }`
                        rule.inject = true
                        rule.on = tool.str2obj(objStr)
                    }
                    if(row.component === 'TAuto' ) {
                        rule.com = 'TAuto'
                        if (row.list) {
                            rule.props.list = tool.str2obj(row.list)
                        }
                        let idField = row.idField
                        searchFields.push({
                            hide: true,
                            field: idField
                        })
                        let objStr = `{change:(inject,val)=>{
                            setTimeout(()=>{inject.$f.setValue('${idField}', val ? val.id : null);},10)
                        } }`
                        rule.inject = true
                        rule.on = tool.str2obj(objStr)
                    }
                    if(row.component === 'JSelectTree' ) {
                        rule.com = 'JSelectTree'
                        if (row.list) {
                            rule.props.list = tool.str2obj(row.list)
                        }
                        if(row.valueKey) {
                            rule.props.valueKey = row.valueKey // 搜索默认使用code值 ，新增使用id
                        }
                        let idField = row.idField
                        searchFields.push({
                            com: 'hidden',
                            field: idField
                        })
                        let objStr = `{change:(inject,val)=>{
                            setTimeout(()=>{inject.$f.setValue('${idField}', val ? val.id : null);},10)
                        } }`
                        rule.inject = true
                        rule.on = tool.str2obj(objStr)
                    }
                    if(row.component === 'jinput' || row.component === 'ltext') {
                        rule.com = 'ltext'
                    }
                    if(row.default) {
                        if(typeof row.vartype === 'number') {
                            rule.value = tool.xe.toNumber(row.default)
                        }
                        else if (row.component === 'JPicker') {
                            rule.value = tool.getDay('', tool.xe.toNumber(row.default))
                        }
                        else {
                            rule.value = row.default
                        }
                    }
                }
                else {
                    if(row.default) {
                        if(row.vartype === 'number') {
                            rule.value = xe.toNumber(row.default)
                        }
                        else {
                            rule.value = row.default
                        }
                    }
                    rule.com = 'hidden'
                }
                searchFields.push(rule)
            }
        })
        return {
            fields: fields,
            searchFields: searchFields
        }
    },
    // 页面配置
    pageSets: async (ctx) => {
        let page = ctx.request.body.whichPage ? ctx.request.body.whichPage : ctx.request.body.page;
        let tenantId = ctx.user.tenantId
        let uid = ctx.user.id

        let formCust = await db.models['formCustV5'].findOne({
            where: {
                page: page
            }
        })
        if (formCust) {
            let model = formCust.toJSON()
            if(model.sqls) {
                model.sqls = model.sqls.replaceAll('${tenantId}', tenantId)
                    .replaceAll('${uid}', uid)
                    .replaceAll('${cby}', uid)
                    .replaceAll('${doId}', uid)
                    .replaceAll('${uby}', uid)
            }
            if(model.fields) {
                model.fields = JSON.parse(model.fields)
            }
            return model
        }
        else {
            return {}
        }
    },
    // 通过数据列表
    getList: async (ctx, sqlStr) => {

        try {
            let pageSize = ctx.request.body.pageSize ? ctx.request.body.pageSize : 200;
            let currentPage = ctx.request.body.currentPage?ctx.request.body.currentPage:1;
            let offset = (currentPage - 1) * pageSize;
            let sort = ctx.request.body.sort; // 组合排序
            let excel = ctx.request.body.excel; // 是否导出

            let formData = ctx.request.body.formData

            let params = await tool.pageSets(ctx)
            let where = []

            // 组合搜索条件
            let fields = params.fields
            let sqls = ''
            if (sqlStr) {
                sqls = sqlStr
            }
            else if (params.sqls) {
                sqls = params.sqls
            }
            if(sqls.includes('${goodCatCode}') !== -1) {
                sqls = sqls.replaceAll('${goodCatCode}', formData.goodCatCode)
            }
            if(sqls) {
                let searchObj = tool.xe.groupBy(fields, 'field')
                if(typeof formData.searchTxt !== 'undefined' && formData.searchTxt) {
                    // 如果列中有searchTxt字段可以搜索，如果没有则搜索name字段
                    if(typeof searchObj.quickSearch !== 'undefined') {
                        where.push(` quickSearch like '%${tool.xe.trim(formData.searchTxt)}%' `)
                    }
                    else {
                        if(typeof searchObj.name !== 'undefined') {
                            where.push(` name like '%${tool.xe.trim(formData.searchTxt)}%' `)
                        }
                        else if(typeof searchObj.invName !== 'undefined') {
                            where.push(` invName like '%${tool.xe.trim(formData.searchTxt)}%' `)
                        }
                        else if(typeof searchObj.buName !== 'undefined') {
                            where.push(` buName like '%${tool.xe.trim(formData.searchTxt)}%' `)
                        }
                        else if(typeof searchObj.billNo !== 'undefined') {
                            where.push(` billNo like '%${tool.xe.trim(formData.searchTxt)}%' `)
                        }
                    }
                    for (let i in formData) {
                        if(typeof searchObj[i] !== 'undefined') {
                            if(searchObj[i][0].condition && !searchObj[i][0].component && searchObj[i][0].default) {
                                where.push(` ${searchObj[i][0].field}='${searchObj[i][0].default}' `)
                            }
                        }
                    }
                }
                else {
                    if(typeof formData.billState !== 'undefined') {
                        if(formData.billState === 'draft') {
                            delete formData.beginDate
                            delete formData.endDate
                        }
                    }
                    for (let i in formData) {
                        if(i && formData[i]) {
                            if(i === 'billState') {
                                if(formData[i] === 'all') {
                                    where.push(` billState in ('accounted', 'draft') `)
                                    continue
                                }
                            }
                            if(i === 'searchTxt') {
                                continue
                            }
                            if(typeof searchObj[i] !== 'undefined' && searchObj[i][0].condition) {
                                let f = i
                                if(i === 'beginDate') f = 'billDate'
                                if(i === 'endDate') f = 'billDate'
                                let str = ''
                                if(searchObj[i][0].component === 'JAuto' || searchObj[i][0].component === 'TAuto' || searchObj[i][0].component === 'JSelectTree') {
                                    if(searchObj[i][0].idField) {
                                        if(searchObj[i][0].cat === 'goodsCat') {
                                            let idField = searchObj[i][0].idField
                                            str = ` goodCatCode `  + searchObj[i][0].condition.replaceAll('$', tool.xe.trim(formData[idField])) +' '

                                        }
                                        else {
                                            let idField = searchObj[i][0].idField
                                            str = ` ${idField} `  + searchObj[i][0].condition.replaceAll('$', tool.xe.trim(formData[idField])) +' '
                                        }
                                    }
                                }
                                else {
                                    str = ' ' + f + ' '  + searchObj[i][0].condition.replaceAll('$', tool.xe.trim(formData[i])) +' '
                                }
                                if(str) {
                                    where.push(str)
                                }
                            }
                        }
                    }
                }
                //
                let ors = []
                if(typeof sort !== 'undefined' && typeof sort === 'object' && sort &&  sort.length > 0) {
                    sort.map(row => {
                        if(row.field === 'durationStr') {
                            ors.push(` duration ${row.order} `)
                        }
                        else {
                            ors.push(` ${row.field} ${row.order} `)
                        }
                    })
                }
                let gp = ' '
                //
                if(where.length > 0) {
                    sqls += ' and ' + where.join(' and ')
                }
                let cols = ' * '
                if(params.cols) {
                    cols = params.cols
                }
                if(params.groupby) {
                    gp = ' group by ' + params.groupby
                }
                let orderby = ' '
                if(ors.length > 0) {
                    orderby += ` order by ${ors.join(' , ')} `
                }
                else {
                    if(params.orderby) {
                        if(params.orderby.indexOf('order') === -1) {
                            orderby += ` order by ${params.orderby} `
                        }
                        else {
                            orderby += ` ${params.orderby} `
                        }
                    }
                    else {
                        orderby += ` order by id desc `
                    }
                }

                if(excel) {
                    let excelSql = `
                        with bills as (${sqls})
                        select ${cols} from bills ${gp} ${orderby} ;
                    `
                    let ret = await db.query(excelSql, {type: QueryTypes.SELECT});
                    return tool.doExcelOut(ctx, ret, params)
                }
                let totalSql = `
                    with bills as (${sqls})
                    select count(*) total 
                    from (select ${cols} from bills ${gp}) a;  ;
                `
                let countModel = await db.query( totalSql, {type: QueryTypes.SELECT, plain: true} );
                let total = countModel.total
                let retSql = `
                    with bills as (${sqls}) 
                    select * from (select ${cols} from bills ${gp}) a ${orderby} offset ${offset} rows fetch next ${pageSize} rows only;
                `

                let ret = await db.query(retSql, {type: QueryTypes.SELECT});
                return ctx.suc('', {
                    total,
                    list: ret
                })
            }
            else {
                return ctx.err('沒有查询数据')
            }
        }
        catch (e) {
            return ctx.err(e)
        }
    },
    doExcelOut: async (ctx, ret, pars, inFields) => {
        try {
            let params = pars ? pars : await tool.pageSets(ctx)
            // 组合搜索条件
            let fields = params.fields

            if(inFields) fields = inFields
            let searchObj = tool.xe.groupBy(fields, 'field')

            if(typeof searchObj.asQty !== 'undefined') {
                ret.map(r => {
                    r.asQty = tool.calAssQty(r.qty, r.spec, r.unit1, r.unit2)
                })
            }
            if(ret.length === 0) return ctx.err('没有要导出的数据')
            let list = [
                {
                    name: params.fileName,
                    data: []
                }
            ]
            let t = []
            let f = []
            fields.map(col => {
                if(col.excelOut) {
                    t.push(col.title)
                    f.push(col.field)
                }
            })
            list[0].data.push(t)
            ret.map(row => {
                let one = []
                _.forEach(f, function (value, key) {
                    if(searchObj[value][0].vartype === 'number') {
                        one.push(tool.xe.toNumber(row[value])===0?'':tool.xe.toNumber(row[value]))
                    }
                    else {
                        one.push(row[value])
                    }
                })
                list[0].data.push(one)
            })
            const buffer = xlsx.build(list)
            let filename = (params.fileName?(params.fileName+'-'):'导出文件-') + ctx.user.id.toString() + _.now() + '.xlsx'
            await fs.writeFileSync(tool.fileSaveDir + '/' + filename, buffer)
            return  ctx.suc('', tool.downUrl + '/web/'  + filename)
        }
        catch (e) {
            return ctx.err(e)
        }
    },
    saveLogInvoice: async (tenantId, uid, id, op, data) => {
        let log = db.models['invoiceLog'].build()
        log.tenantId = tenantId
        log.op = op
        log.iid = id
        log.uid = uid
        log.data = JSON.stringify(data)
        log.ctime = tool.getTime()
        await log.save()
    },
    // 检测是否账户已开始，开始后不能修改数据
    isZqStart: async (ctx) => {
        let started = await db.models['zq'].findOne({
            where: {
                tenantId: ctx.user.tenantId
            }
        })
        if(started) {
            throw Error('账期已开始,不能再修改期初')
        }
    },
    beforeAction: async (sets, ctx) => {
        let beforeActions = sets.beforeAction ?  new Function('_ctx','return ' + sets.beforeAction)(ctx)  : []
        for (let i = 0; i < beforeActions.length; i++) {
            if(beforeActions[i].action) {
                await tool[beforeActions[i].action](ctx)
            }
        }
    },
    getSort: (ctx) => {
        let sort = ctx.request.body.sort
        sort = sort ? sort : []
        let ors = []
        if(sort && sort.length > 0) {
            sort.map(row => {
                if(row.field === 'durationStr') {
                    ors.push(` duration ${row.order} `)
                }
                else {
                    ors.push(` ${row.field} ${row.order} `)
                }
            })
        }
        if(ors.length > 0) {
            return ors.join(',')
        }
        return ''
    },
    getGoodCode: async (ctx) => {
        let goodCatId = ''
        if(ctx.request.body.formData && ctx.request.body.formData.goodCatId) {
            goodCatId = ctx.request.body.formData.goodCatId
        }
        else {
            goodCatId = ctx.request.body.goodCatId?ctx.request.body.goodCatId:''
        }
        if(goodCatId) {
            let code = await db.models['goodsCat'].findOne({
                where: {
                    tenantId: ctx.user.tenantId,
                    id: goodCatId
                }
            })
            return  code.code
        }
        return ''
    },
    getSubjectByName: async (tenantId, km) => {
        let arr = []
        km.map(row => {
            arr.push(`'${row.id}'`)
        })
        let sql = `
        SELECT b.id,b.tenantId,b.name,b.code from 
        (
        SELECT tenantId, code from category where tenantId=${tenantId} and typeNumber='subject' and name in(${km.join(',')})
        ) a 
        left join category b on a.tenantId=b.tenantId  and b.code like a.code+'%' 
         where b.tenantId=${tenantId}  and b.typeNumber='subject' and b.id is not null ORDER BY b.code 
        `
        let list = await db.query(sql, {type: QueryTypes.SELECT})
        let ret = []
        list.map(row => {
            ret.push(row.id)
        })
        return ret
    },
    checkPhone: (phone) => {
        if(!(/^1[3456789]\d{9}$/.test(phone))){
            return false;
        }
        return true;
    },
    getUnits: async () => {
        let units = []
        let unitM = await db.models['unit'].findAll();
        unitM.map(row => {
            let u = row.toJSON()
            units.push({id: u.id, name: u.name})
        })
        return units
    },

}




module.exports = tool